﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.Record;//NPOI.HSSF.Record.Formula.Eval改为了NPOI.SS.Formula.Eval;
using NPOI.SS.Formula.Eval;//同上
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.SS;
using NPOI.DDF;
using NPOI.SS.Util;
using System.Collections;
using System.Text.RegularExpressions;
using EasyFMS.Common;
using Liger.Data;

public class ExcelHelper
{

    public static DbContext DB = DbHelper.Db;

    /// <summary>
    /// 类版本
    /// </summary>
    public string version
    {
        get { return "1.0"; }
    }

    static readonly int EXCEL03_MaxRow = 65535;

    #region 从datatable中将数据导出到excel
    /// <summary>
    /// DataTable导出到Excel的MemoryStream
    /// </summary>
    /// <param name="dtSource">源DataTable</param>
    /// <param name="strHeaderText">表头文本</param>
    static MemoryStream ExportDataTable(DataTable dtSource, string strHeaderText)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

        HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
        HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

        //取得列宽
        int[] arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;

        foreach (DataRow row in dtSource.Rows)
        {
            #region 新建表，填充表头，填充列头，样式

            if (rowIndex == 65535 || rowIndex == 0)
            {
                if (rowIndex != 0)
                {
                    sheet = workbook.CreateSheet() as HSSFSheet;
                }

                #region 表头及样式

                {
                    HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(strHeaderText);
                    HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                    HSSFFont font = workbook.CreateFont() as HSSFFont;
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    headerRow.GetCell(0).CellStyle = headStyle;
                    sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));

                }

                #endregion


                #region 列头及样式

                {
                    HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
                    HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                    HSSFFont font = workbook.CreateFont() as HSSFFont;
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        //设置列宽
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                    }
                }

                #endregion
                rowIndex = 2;
            }

            #endregion

            #region 填充内容
            HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
            foreach (DataColumn column in dtSource.Columns)
            {
                HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                string drValue = row[column].ToString();
                switch (column.DataType.ToString())
                {
                    case "System.String": //字符串类型
                        double result;
                        if (isNumeric(drValue, out result))
                        {
                            double.TryParse(drValue, out result);
                            newCell.SetCellValue(result);
                            break;
                        }
                        else
                        {
                            newCell.SetCellValue(drValue);
                            break;
                        }

                    case "System.DateTime": //日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);
                        newCell.CellStyle = dateStyle; //格式化显示
                        break;
                    case "System.Boolean": //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16": //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;
                    case "System.Decimal": //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull": //空值处理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }

            }

            #endregion
            rowIndex++;
        }
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }
    }

    /// <summary>
    /// DataTable导出到Excel文件
    /// </summary>
    /// <param name="dtSource">源DataTable</param>
    /// <param name="strHeaderText">表头文本</param>
    /// <param name="strFileName">保存位置</param>
    public static void ExportDataTableToExcel(DataTable dtSource, string strHeaderText, string strFileName)
    {
        using (MemoryStream ms = ExportDataTable(dtSource, strHeaderText))
        {
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }
    #endregion

    #region 从excel中将数据导出到datatable
    /// <summary>读取excel
    /// 默认第一行为标头
    /// </summary>
    /// <param name="strFileName">excel文档路径</param>
    /// <returns></returns>
    public static DataTable ImportExcelToDataTable(string strFileName)
    {
        DataTable dt = new DataTable();
        HSSFWorkbook hssfworkbook;
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            hssfworkbook = new HSSFWorkbook(file);
        }
        HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
        dt = ImportDataTable(sheet, 0, true);
        return dt;
    }
    /// <summary>
    /// 读取EXCEL
    /// </summary>
    /// <param name="strFileName">文件名</param>
    /// <param name="SheetIndex">读取的起始行</param>
    /// <returns></returns>
    public static DataTable ImportExcelToDataTable(string strFileName, int SheetIndex)
    {
        DataTable dt = new DataTable();
        HSSFWorkbook hssfworkbook;
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            hssfworkbook = new HSSFWorkbook(file);
        }
        HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
        dt = ImportDataTable(sheet, SheetIndex, true);
        return dt;
    }
    /// <summary>
    /// 读取excel
    /// </summary>
    /// <param name="strFileName">excel文件路径</param>
    /// <param name="sheet">需要导出的sheet</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <returns></returns>
    public static DataTable ImportExcelToDataTable(string strFileName, string SheetName, int HeaderRowIndex)
    {
        HSSFWorkbook workbook;
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook(file);
        }
        HSSFSheet sheet = workbook.GetSheet(SheetName) as HSSFSheet;
        DataTable table = new DataTable();
        table = ImportDataTable(sheet, HeaderRowIndex, true);
        //ExcelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }

    /// <summary>
    /// 读取excel
    /// </summary>
    /// <param name="strFileName">excel文件路径</param>
    /// <param name="sheet">需要导出的sheet序号</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <returns></returns>
    public static DataTable ImportExcelToDataTable(string strFileName, int SheetIndex, int HeaderRowIndex)
    {
        HSSFWorkbook workbook;
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook(file);
        }
        HSSFSheet sheet = workbook.GetSheetAt(SheetIndex) as HSSFSheet;
        DataTable table = new DataTable();
        table = ImportDataTable(sheet, HeaderRowIndex, true);
        //ExcelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }

    /// <summary>
    /// 读取excel
    /// </summary>
    /// <param name="strFileName">excel文件路径</param>
    /// <param name="sheet">需要导出的sheet</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <returns></returns>
    public static DataTable ImportExcelToDataTable(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)
    {
        HSSFWorkbook workbook;
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook(file);
        }
        HSSFSheet sheet = workbook.GetSheet(SheetName) as HSSFSheet;
        DataTable table = new DataTable();
        table = ImportDataTable(sheet, HeaderRowIndex, needHeader);
        //ExcelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }

    /// <summary>
    /// 读取excel
    /// </summary>
    /// <param name="strFileName">excel文件路径</param>
    /// <param name="sheet">需要导出的sheet序号</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <returns></returns>
    public static DataTable ImportExcelToDataTable(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
    {
        HSSFWorkbook workbook;
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook(file);
        }
        HSSFSheet sheet = workbook.GetSheetAt(SheetIndex) as HSSFSheet;
        DataTable table = new DataTable();
        table = ImportDataTable(sheet, HeaderRowIndex, needHeader);
        //ExcelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }

    /// <summary>
    /// 将指定的sheet中的数据导出到datatable中
    /// </summary>
    /// <param name="sheet">需要导出的sheet</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <returns></returns>
    static DataTable ImportDataTable(HSSFSheet sheet, int HeaderRowIndex, bool needHeader)
    {
        DataTable table = new DataTable();
        HSSFRow headerRow;
        int cellCount;
        try
        {
            if (HeaderRowIndex < 0 || !needHeader)
            {
                headerRow = sheet.GetRow(0) as HSSFRow;
                cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    DataColumn column = new DataColumn(Convert.ToString(i));
                    table.Columns.Add(column);
                }
            }
            else
            {
                headerRow = sheet.GetRow(HeaderRowIndex) as HSSFRow;
                cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    if (headerRow.GetCell(i) == null)
                    {
                        if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(Convert.ToString(i));
                            table.Columns.Add(column);
                        }

                    }
                    else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                    {
                        DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                        table.Columns.Add(column);
                    }
                    else
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                        table.Columns.Add(column);
                    }
                }
            }
            int rowCount = sheet.LastRowNum;
            for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
            {

                HSSFRow row;
                if (sheet.GetRow(i) == null)
                {
                    row = sheet.CreateRow(i) as HSSFRow;
                }
                else
                {
                    row = sheet.GetRow(i) as HSSFRow;
                }

                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j <= cellCount; j++)
                {

                    if (row.GetCell(j) != null)
                    {
                        SwitchCellType(row, dataRow, j);
                    }
                }
                table.Rows.Add(dataRow);
            }

        }
        catch (Exception exceptionMsg)
        {
            
        }
        return table;
    }

    private static void SwitchCellType(HSSFRow row, DataRow dataRow, int j)
    {
        switch (row.GetCell(j).CellType)
        {
            case CellType.STRING:
                string str = row.GetCell(j).StringCellValue;
                if (str != null && str.Length > 0)
                {
                    dataRow[j] = str.ToString();
                }
                else
                {
                    dataRow[j] = null;
                }
                break;
            case CellType.NUMERIC:
                if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                {
                    dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                }
                else
                {
                    dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                }
                break;
            case CellType.BOOLEAN:
                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                break;
            case CellType.ERROR:
                dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                break;
            case CellType.FORMULA:
                switch (row.GetCell(j).CachedFormulaResultType)
                {
                    case CellType.STRING:
                        string strFORMULA = row.GetCell(j).StringCellValue;
                        if (strFORMULA != null && strFORMULA.Length > 0)
                        {
                            dataRow[j] = strFORMULA.ToString();
                        }
                        else
                        {
                            dataRow[j] = null;
                        }
                        break;
                    case CellType.NUMERIC:
                        dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                        break;
                    case CellType.BOOLEAN:
                        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                        break;
                    case CellType.ERROR:
                        dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                        break;
                    default:
                        dataRow[j] = "";
                        break;
                }
                break;
            default:
                dataRow[j] = "";
                break;
        }
    }
    #endregion

    #region 更新excel中的数据
    /// <summary>
    /// 更新Excel表格
    /// </summary>
    /// <param name="outputFile">需更新的excel表格路径</param>
    /// <param name="sheetname">sheet名</param>
    /// <param name="updateData">需更新的数据</param>
    /// <param name="coluid">需更新的列号</param>
    /// <param name="rowid">需更新的开始行号</param>
    public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid)
    {
        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

        HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
        for (int i = 0; i < updateData.Length; i++)
        {
            try
            {
                if (sheet1.GetRow(i + rowid) == null)
                {
                    sheet1.CreateRow(i + rowid);
                }
                if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                {
                    sheet1.GetRow(i + rowid).CreateCell(coluid);
                }

                sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
            }
            catch (Exception ex)
            {
                // wl.WriteLogs(ex.ToString());
                throw;
            }
        }
        try
        {
            readfile.Close();
            FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
            hssfworkbook.Write(writefile);
            writefile.Close();
        }
        catch (Exception ex)
        {
            // wl.WriteLogs(ex.ToString());
        }

    }

    /// <summary>
    /// 更新Excel表格
    /// </summary>
    /// <param name="outputFile">需更新的excel表格路径</param>
    /// <param name="sheetname">sheet名</param>
    /// <param name="updateData">需更新的数据</param>
    /// <param name="coluids">需更新的列号</param>
    /// <param name="rowid">需更新的开始行号</param>
    public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
    {
        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

        HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
        readfile.Close();
        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
        for (int j = 0; j < coluids.Length; j++)
        {
            for (int i = 0; i < updateData[j].Length; i++)
            {
                try
                {
                    if (sheet1.GetRow(i + rowid) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                    {
                        sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                    }
                    sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                }
                catch (Exception ex)
                {
                    // wl.WriteLogs(ex.ToString());
                }
            }
        }
        try
        {
            FileStream writefile = new FileStream(outputFile, FileMode.Create);
            hssfworkbook.Write(writefile);
            writefile.Close();
        }
        catch (Exception ex)
        {
            //wl.WriteLogs(ex.ToString());
        }
    }

    /// <summary>
    /// 更新Excel表格
    /// </summary>
    /// <param name="outputFile">需更新的excel表格路径</param>
    /// <param name="sheetname">sheet名</param>
    /// <param name="updateData">需更新的数据</param>
    /// <param name="coluid">需更新的列号</param>
    /// <param name="rowid">需更新的开始行号</param>
    public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
    {
        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

        HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
        for (int i = 0; i < updateData.Length; i++)
        {
            try
            {
                if (sheet1.GetRow(i + rowid) == null)
                {
                    sheet1.CreateRow(i + rowid);
                }
                if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                {
                    sheet1.GetRow(i + rowid).CreateCell(coluid);
                }

                sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
                throw;
            }
        }
        try
        {
            readfile.Close();
            FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
            hssfworkbook.Write(writefile);
            writefile.Close();
        }
        catch (Exception ex)
        {
            //wl.WriteLogs(ex.ToString());
        }

    }

    /// <summary>
    /// 更新Excel表格
    /// </summary>
    /// <param name="outputFile">需更新的excel表格路径</param>
    /// <param name="sheetname">sheet名</param>
    /// <param name="updateData">需更新的数据</param>
    /// <param name="coluids">需更新的列号</param>
    /// <param name="rowid">需更新的开始行号</param>
    public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)
    {
        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

        HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
        readfile.Close();
        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
        for (int j = 0; j < coluids.Length; j++)
        {
            for (int i = 0; i < updateData[j].Length; i++)
            {
                try
                {
                    if (sheet1.GetRow(i + rowid) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                    {
                        sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                    }
                    sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                }
                catch (Exception ex)
                {
                    //wl.WriteLogs(ex.ToString());
                }
            }
        }
        try
        {
            FileStream writefile = new FileStream(outputFile, FileMode.Create);
            hssfworkbook.Write(writefile);
            writefile.Close();
        }
        catch (Exception ex)
        {
            //wl.WriteLogs(ex.ToString());
        }
    }

    #endregion

    public static int GetSheetNumber(string outputFile)
    {
        int number = 0;
        try
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            number = hssfworkbook.NumberOfSheets;

        }
        catch (Exception exceptionMsg)
        {
            

        }
        return number;
    }

    public static ArrayList GetSheetName(string outputFile)
    {
        ArrayList arrayList = new ArrayList();
        try
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
            {
                arrayList.Add(hssfworkbook.GetSheetName(i));
            }
        }
        catch (Exception exception)
        {
            //wl.WriteLogs(exception.ToString());
        }
        return arrayList;
    }

    public static bool isNumeric(String message, out double result)
    {
        Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
        result = -1;
        if (rex.IsMatch(message))
        {
            result = double.Parse(message);
            return true;
        }
        else
            return false;

    }

    public static bool HasData(Stream excelFileStream)
    {
        using (excelFileStream)
        {
            IWorkbook workbook = new HSSFWorkbook(excelFileStream);
            if (workbook.NumberOfSheets > 0)
            {
                ISheet sheet = workbook.GetSheetAt(0);
                return sheet.PhysicalNumberOfRows > 0;
            }
        }
        return false;
    }

    public static int RenderToDb(Stream excelFileStream, string insertSql, Dictionary<string, string> columnArr, int userid)
    {
        int rowAffected = 0;
        try
        {
            using (excelFileStream)
            {
                IWorkbook workbook = new HSSFWorkbook(excelFileStream);
                ISheet sheet = workbook.GetSheetAt(0);
                StringBuilder builder = new StringBuilder();
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        builder.Append(insertSql);
                        builder.Append(" VALUES (");
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            string valKeys = GetColumnLabel(j);
                            if (columnArr.ContainsKey(valKeys))
                            {
                                if (columnArr[valKeys].ToString() == "字符型")
                                {
                                    builder.AppendFormat("'{0}',", GetCellVal((HSSFRow)row, j));
                                }
                                else
                                {
                                    builder.AppendFormat("{0},", GetCellVal((HSSFRow)row, j));
                                }
                            }
                        }
                        builder.Append(userid.ToString()).Append(",getdate());");
                    }

                    if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
                    {
                        //每50条记录一次批量插入到数据库
                        rowAffected += DB.ExecuteNonQuery(builder.ToString());
                        builder.Length = 0;
                    }
                }
            }
        }
        catch (Exception aaa)
        {
            rowAffected = -1;
        }
        return rowAffected;
    }

    //获取一列对应的字母。例如：ColumnNum=1，则返回值为A 列号转字母
    public static string GetColumnLabel(int ColumnNum)
    {
        string temp =string.Empty;
        double i = Math.Floor(Math.Log(25.0*(ColumnNum)/26.0 + 1) / Math.Log(26)) + 1; 
        if(i > 1)
        {
            double sub = ColumnNum - 26*(Math.Pow(26,i-1) - 1)/25;
            for(double j =i;j> 0 ;j--)
            {
                temp = temp + (char)(sub/Math.Pow(26,j-1)+65);
                sub = sub % Math.Pow(26,j-1);
            }
        }
        else{
            temp = temp + (char)(ColumnNum+65);
        }
        return temp.ToUpper();
    }

    private static object GetCellVal(HSSFRow row, int j)
    {
        object returnVal = new object();
        switch (row.GetCell(j).CellType)
        {
            case CellType.STRING:
                returnVal = row.GetCell(j).StringCellValue;
                break;
            case CellType.NUMERIC:
                if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                {
                    returnVal = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                }
                else
                {
                    returnVal = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                }
                break;
            case CellType.BOOLEAN:
                returnVal = Convert.ToString(row.GetCell(j).BooleanCellValue);
                break;
            case CellType.ERROR:
                returnVal = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                break;
            case CellType.FORMULA:
                switch (row.GetCell(j).CachedFormulaResultType)
                {
                    case CellType.STRING:
                        string strFORMULA = row.GetCell(j).StringCellValue;
                        if (strFORMULA != null && strFORMULA.Length > 0)
                        {
                            returnVal = strFORMULA.ToString();
                        }
                        else
                        {
                            returnVal = null;
                        }
                    break;
                    case CellType.NUMERIC:
                        returnVal = Convert.ToString(row.GetCell(j).NumericCellValue);
                    break;
                    case CellType.BOOLEAN:
                        returnVal = Convert.ToString(row.GetCell(j).BooleanCellValue);
                    break;
                    case CellType.ERROR:
                        returnVal = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                    break;
                    default:
                        returnVal = "";
                    break;
                }
                break;
            default:
                returnVal = "";
                break;
        }
        if (String.IsNullOrEmpty(returnVal.ToString()))
        {
            return "";
        }
        return returnVal;
    }

    /// <summary>
    /// 将DataTable转换为excel2003格式。
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static byte[] DataTable2Excel(DataTable dt, string sheetName)
    {
        IWorkbook book = new HSSFWorkbook();
        if (dt.Rows.Count < EXCEL03_MaxRow)
            DataWrite2Sheet(dt, 0, dt.Rows.Count - 1, book, sheetName);
        else
        {
            int page = dt.Rows.Count / EXCEL03_MaxRow;
            for (int i = 0; i < page; i++)
            {
                int start = i * EXCEL03_MaxRow;
                int end = (i * EXCEL03_MaxRow) + EXCEL03_MaxRow - 1;
                DataWrite2Sheet(dt, start, end, book, sheetName + i.ToString());
            }
            int lastPageItemCount = dt.Rows.Count % EXCEL03_MaxRow;
            DataWrite2Sheet(dt, dt.Rows.Count - lastPageItemCount, lastPageItemCount, book, sheetName + page.ToString());
        }
        MemoryStream ms = new MemoryStream();
        book.Write(ms);
        return ms.ToArray();
    }

    private static void DataWrite2Sheet(DataTable dt, int startRow, int endRow, IWorkbook book, string sheetName)
    {
        ISheet sheet = book.CreateSheet(sheetName);
        IRow header = sheet.CreateRow(0);
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            ICell cell = header.CreateCell(i);
            string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
            cell.SetCellValue(val);
        }

        int rowIndex = 1;
        for (int i = startRow; i <= endRow; i++)
        {
            DataRow dtRow = dt.Rows[i];
            IRow excelRow = sheet.CreateRow(rowIndex++);
            for (int j = 0; j < dtRow.ItemArray.Length; j++)
            {
                excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
            }
        }
    }

    /// <summary>
    /// 用于Web导出
    /// </summary>
    /// <param name="dtSource"></param>
    /// <param name="strHeaderText"></param>
    /// <param name="strFileName"></param>
    public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
    {
        HttpContext curContext = HttpContext.Current;
        curContext.Response.ContentType = "application/vnd.ms-excel";
        curContext.Response.ContentEncoding = Encoding.UTF8;
        curContext.Response.Charset = "";
        curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
        curContext.Response.BinaryWrite(DataTable2Excel(dtSource,strHeaderText));
        curContext.Response.Flush();
        curContext.Response.End();
    }
}



